/**
 * Copyright (c) 2007,中企动力华南研发中心<br>
 * All rights reserved.
 */
package com.ce.pms.renovation.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.renovation.dao.IRenovateLicenceDao;
import com.ce.pms.renovation.model.PassChargeItemDO;
import com.ce.pms.renovation.model.PassDO;
import com.ce.pms.renovation.model.RenovateChargeItemDO;
import com.ce.pms.renovation.model.RenovateLicenceDO;
import com.ce.pms.renovation.vo.LicenceListVO;
import com.ce.pms.renovation.vo.LicenceQueryVO;
import com.ce.pms.renovation.vo.RenovateRequisitionVO;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

/**
 * 功能描述：<br>
 * @author 罗军林 创建日期：2008-12-25
 */
public class RenovateLicenceDaoImpl extends PmsBaseDaoImpl<RenovateLicenceDO> implements IRenovateLicenceDao{
	private final String QUERY_LICENCES="queryLicences";
	private final String GET_MAX_PASS_NUMBER="getMaxPassNumber";
	private SqlConfig sqlconfig=null;

	public RenovateLicenceDaoImpl(){
		sqlconfig=new SqlConfig("RenovateSQL.xml");
	}

	/**
	 * 功能描述:查询指定许可证的收费项目列表<br>
	 * @param passId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2009-1-9<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public List<PassChargeItemDO> getPassChargeItemDOs(Long passId){
		String hql="FROM PassChargeItemDO item where item.pass.id=?";
		Query query=createQuery(hql, passId);
		return query.list();
	}

	/**
	 * 功能描述:查询指定装修申请的许可证<br>
	 * @param requisitionID
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-25<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public RenovateLicenceDO getRenovateLicenceByRequisitionID(Long requisitionID) {
		String hql="FROM RenovateLicenceDO lic where lic.renovateRequisition.keyword=?";
		Query query=createQuery(hql, requisitionID);
		return (RenovateLicenceDO)query.uniqueResult();
	}

	/**
	 * 功能描述：查询指定房间的有效的许可证<br>
	 * @param houseId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-30<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public RenovateLicenceDO getRequisitionLicenceOutline(Long houseId) {
		String hql="FROM RenovateLicenceDO lic where (lic.renovateRequisition.processState!=? and lic.renovateRequisition.processState!=?) and lic.renovateRequisition.house.id=?";
		Query query=createQuery(hql,SysrefcodeDO.RM_RENOVATE_PROCESS_STATE_NOR,SysrefcodeDO.RM_RENOVATE_PROCESS_STATE_EXP,houseId);
		return (RenovateLicenceDO)query.uniqueResult();
	}

	/**
	 * 功能描述:许可证查询<br>
	 * @param licenceQueryVO
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-29<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public Page queryLicences(LicenceQueryVO licenceQueryVO){
		Map<String, Object> parameter=new HashMap<String, Object>();
		String sql=sqlconfig.getSql(QUERY_LICENCES);
		sql=sql.replaceAll("_queryCondition", buildCondition(parameter,licenceQueryVO));
		sql=sql.replaceAll("_order", buildOrder(licenceQueryVO));
		Page page= pagedSqlNameQueryByPageNo(sql, licenceQueryVO.getPageNo(), licenceQueryVO.getPageSize(), parameter);
		page.setResult( assembleLicenceO2V(page.getResult()) );
		return page;
	}

	/**
	 * 功能描述:查询指定许可证的出入证<br>
	 * @param licenceId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-29<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public List<PassDO> getPasses(Long licenceId){
		 String hql="FROM PassDO pass WHERE pass.renovateLicence.id=? order by pass.id desc";
		 Query query=createQuery(hql, licenceId);
		 return query.list();
	 }

	/**
	 * 功能描述:获取当天最大的出入证号<br>
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2009-1-4<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public String getMaxPassNumber() {
		String sql=sqlconfig.getSql(GET_MAX_PASS_NUMBER);
		Query query = super.createSQLQuery(sql,null);
		Object obj = query.uniqueResult();
		if(BlankUtil.isBlank(obj))
			return null;
		String count = (String) obj;
		return count;
	}

	/**
	 * 功能描述:将原生SQL查询的许可证对象数组转成许可证VO<br>
	 * @param result
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private List<LicenceListVO> assembleLicenceO2V(List<Object[]> result) {
		List<LicenceListVO> temp=new ArrayList<LicenceListVO>();
		for(Object[] item : result){
			LicenceListVO licenceListVO=new LicenceListVO();
			licenceListVO.setLicenceNumber( (String)item[0] );//许可证号
			licenceListVO.setHouseNumber( (String)item[1] );//房号
			licenceListVO.setClientNames( (String)item[2] );//业主姓名
			licenceListVO.setRenovateLicenceState( (String)item[3] );//许可证状态
			licenceListVO.setBurdenMan( (String)item[4] );//现场负责人
			licenceListVO.setBurdenManPhone( (String)item[5] );//联系电话
			licenceListVO.setBeginDate( (Date)item[6] );//装修开始日期
			licenceListVO.setEndDate( (Date)item[7] );//装修结束日期
			licenceListVO.setFeeState( (String)item[8] );//费用状态
			licenceListVO.setIssueCount( item[9]==null ? null : ((BigInteger)item[9]).intValue() );//发证数量
			licenceListVO.setReturnCount( item[10]==null ? null : ((BigDecimal)item[10]).intValue() );//退证数量
			licenceListVO.setChargeCount( item[10]==null ? null : ((BigDecimal)item[11]).longValue() ); //发证并已经收费的数量
			licenceListVO.setLicenceId( ((BigInteger)item[12]).longValue() );

			temp.add(licenceListVO);
		}
		return temp;
	}

	/**
	 * 功能描述:构建查询条件<br>
	 * @param parameter
	 * @param queryCondition
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildCondition(final Map<String, Object> parameter,final LicenceQueryVO licenceQueryVO){
		StringBuilder str=new StringBuilder();
		//楼栋
		if(licenceQueryVO.getBuildingId()!=null){
			str.append(" and HB.BUILDING_ID=:buildingId ");
			parameter.put("buildingId", licenceQueryVO.getBuildingId());
		}

		//房号
		if(!BlankUtil.isBlank(licenceQueryVO.getHouseNumber())){
			str.append(" and HM.HOUSE_NUMBER like :houseNumber ");
			parameter.put("houseNumber", "%" + licenceQueryVO.getHouseNumber().trim()+"%" );
		}
		return str.toString();
	}

	/**
	 * 功能描述:<br>
	 * @param orderStr
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildOrder(LicenceQueryVO licenceQueryVO){
		if(null==licenceQueryVO.getOrderValue()){
			return " ORDER BY LIC.SYS_DATE DESC ";
		}else if(licenceQueryVO.getOrderValue().equalsIgnoreCase("houseNumber")
				&&  licenceQueryVO.getOrderKey().equalsIgnoreCase("asc")){
			return " ORDER BY HB.SEQ_NO ASC,FL.STOREY_NUMBER ASC,HM.HOUSE_NUMBER ASC";
		}else if(licenceQueryVO.getOrderValue().equalsIgnoreCase("houseNumber")
				&&  licenceQueryVO.getOrderKey().equalsIgnoreCase("desc")){
			return " ORDER BY HB.SEQ_NO DESC,FL.STOREY_NUMBER DESC,HM.HOUSE_NUMBER DESC";
		}else{
			return " ORDER BY LIC.SYS_DATE DESC ";
		}
	}
}
